package com.ctshk.rpc.scenic.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.rpc.scenic.dto.ApplyUserInfoDTO;
import com.ctshk.rpc.scenic.dto.TicketTypeLuckDrawUserDTO;
import com.ctshk.rpc.scenic.entity.TicketTypeLuckDrawUser;
import com.ctshk.rpc.scenic.req.ApplyUserInfoReq;
import com.ctshk.rpc.scenic.req.RegistrationExportReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 抽奖用户关联表 Mapper 接口
 * </p>
 *
 * @author 谢诗宏
 * @since 2021-02-18
 */
public interface TicketTypeLuckDrawUserMapper extends BaseMapper<TicketTypeLuckDrawUser> {


    @Select("<script>SELECT  \n" +
            "            u.query_number queryNumber, \n" +
            "            u.en_name enName,  \n" +
            "            u.cn_name cnName,  \n" +
            "            u.phone phone,  \n" +
            "            u.agree agree,  \n" +
            "            COUNT(t.session_code) sessionNumber,  \n" +
            "            SUM( tu.number ) number, \n" +
            "            tu.id ticketTypeLuckDrawUserId, \n" +
            "            u.gmt_create gmtCreate,   \n" +
            "            t.fare_basis fareBasis   \n" +
            "            FROM  \n" +
            "            t_ticket_luck_draw_apply_user_info u  \n" +
            "            LEFT JOIN t_ticket_type_luck_draw_user tu ON u.id = tu.user_info_id  \n" +
            "            LEFT JOIN t_ticket_type_luck_draw t ON  t.id  = tu.ticket_type_id \n" +
            "            <where>   \n" +
            "            <if test=\"applyUserInfoReq.queryNumber != null and applyUserInfoReq.queryNumber !=''\">  \n" +
            "             u.query_number like concat('%',#{applyUserInfoReq.queryNumber},'%') \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.startTime != null and applyUserInfoReq.startTime !=''\">  \n" +
            "             u.gmt_create BETWEEN #{applyUserInfoReq.startTime} and #{applyUserInfoReq.endTime} \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.phone != null and applyUserInfoReq.phone !=''\">  \n" +
            "            and u.phone like concat('%',#{applyUserInfoReq.phone},'%') \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.agree != null\">  \n" +
            "            and u.agree = #{applyUserInfoReq.agree}  \n" +
            "            </if>  \n" +
            "            <if test =\"applyUserInfoReq.status != null\">  \n" +
            "            and t.status = #{applyUserInfoReq.status}  \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.discipline != null and applyUserInfoReq.discipline !=''\">  \n" +
            "             and t.discipline like concat('%',#{applyUserInfoReq.discipline},'%') \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.sessionsName != null and applyUserInfoReq.sessionsName !=''\">  \n" +
            "             and t.sessions_name like concat('%',#{applyUserInfoReq.sessionsName},'%') \n" +
            "            </if>  \n" +
            "            </where> GROUP BY u.query_number " +
            "            ORDER BY u.gmt_create DESC" +
            "           </script>")
    IPage<ApplyUserInfoDTO> FindApplyUser(@Param("applyUserInfoReq") ApplyUserInfoReq applyUserInfoReq, IPage<ApplyUserInfoDTO> page);

    @Select("<script>SELECT  \n" +
            "            u.query_number queryNumber, \n" +
            "            u.en_name enName,  \n" +
            "            u.cn_name cnName,  \n" +
            "            u.phone phone,  \n" +
            "            u.agree agree,  \n" +
            "            COUNT(t.session_code) sessionNumber,  \n" +
            "            SUM( tu.number ) number, \n" +
            "            tu.id ticketTypeLuckDrawUserId, \n" +
            "            u.gmt_create gmtCreate,   \n" +
            "            t.fare_basis fareBasis   \n" +
            "            FROM  \n" +
            "            t_ticket_luck_draw_apply_user_info u  \n" +
            "            LEFT JOIN t_ticket_type_luck_draw_user tu ON u.id = tu.user_info_id  \n" +
            "            LEFT JOIN t_ticket_type_luck_draw t ON  t.id  = tu.ticket_type_id \n" +
            "            <where>   \n" +
            "            <if test=\"applyUserInfoReq.queryNumber != null and applyUserInfoReq.queryNumber !=''\">  \n" +
            "             u.query_number like concat('%',#{applyUserInfoReq.queryNumber},'%') \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.startTime != null and applyUserInfoReq.startTime !=''\">  \n" +
            "             u.gmt_create BETWEEN #{applyUserInfoReq.startTime} and #{applyUserInfoReq.endTime} \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.phone != null and applyUserInfoReq.phone !=''\">  \n" +
            "            and u.phone like concat('%',#{applyUserInfoReq.phone},'%') \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.agree != null\">  \n" +
            "            and u.agree = #{applyUserInfoReq.agree}  \n" +
            "            </if>  \n" +
            "            <if test =\"applyUserInfoReq.status != null\">  \n" +
            "            and t.status = #{applyUserInfoReq.status}  \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.discipline != null and applyUserInfoReq.discipline !=''\">  \n" +
            "             and t.discipline like concat('%',#{applyUserInfoReq.discipline},'%') \n" +
            "            </if>  \n" +
            "            <if test=\"applyUserInfoReq.sessionsName != null and applyUserInfoReq.sessionsName !=''\">  \n" +
            "             and t.sessions_name like concat('%',#{applyUserInfoReq.sessionsName},'%') \n" +
            "            </if>  \n" +
            "            </where> GROUP BY u.query_number " +
            "            ORDER BY u.gmt_create DESC" +
            "           </script>")
    List<ApplyUserInfoDTO> queryAllApplyUser(@Param("applyUserInfoReq") RegistrationExportReq applyUserInfoReq);

    @Select("SELECT\n" +
            "\tdu.*,\n" +
            "\tdr.fare_basis,\n" +
            "\tdr.session_code,\n" +
            "\tinfo.agree \n" +
            "FROM\n" +
            "\tt_ticket_type_luck_draw_user du\n" +
            "\tLEFT JOIN t_ticket_type_luck_draw dr ON du.ticket_type_id = dr.id\n" +
            "\tLEFT JOIN t_ticket_luck_draw_apply_user_info info ON du.user_info_id = info.id \n" +
            "WHERE\n" +
            "\tdu.winning = 0 ORDER BY du.number DESC")
    List<TicketTypeLuckDrawUserDTO> selectDrawUserList();

    @Update("update t_ticket_type_luck_draw_user set winning = #{winning} WHERE id in (${ids})")
    int updateByIds(@Param("ids") String ids, @Param("winning") Integer winning);
}
